package com.molichuxing.gateway.bff.car.services.impl;

import cn.hutool.core.date.DatePattern;
import com.molichuxing.framework.dto.Paged;
import com.molichuxing.framework.dto.UploadFileRequestDto;
import com.molichuxing.framework.dto.UploadFileResponseDto;
import com.molichuxing.framework.exception.BizException;
import com.molichuxing.framework.result.ResponseCodeEnum;
import com.molichuxing.framework.utils.DateUtil;
import com.molichuxing.framework.utils.*;
import com.molichuxing.gateway.bff.basic.services.FileStorageBffService;
import com.molichuxing.gateway.bff.car.convertor.Convert;
import com.molichuxing.gateway.bff.car.entity.request.modify.CarCommercialInsuranceModifyVo;
import com.molichuxing.gateway.bff.car.entity.request.modify.CarCompulsoryInsuranceModifyVo;
import com.molichuxing.gateway.bff.car.entity.response.CarInsuranceDetailVo;
import com.molichuxing.gateway.bff.car.entity.response.CarInsuranceVo;
import com.molichuxing.gateway.bff.car.services.InsuranceBffService;
import com.molichuxing.gateway.property.OSSFileFolderEnum;
import com.molichuxing.gateway.utils.TokenUtil;
import com.molichuxing.gateway.utils.entity.UserEntity;
import com.molichuxing.services.business.dto.response.CarInsuranceDetailDto;
import com.molichuxing.services.business.dto.response.CarInsuranceDto;
import com.molichuxing.services.business.service.CarInsuranceBizService;
import com.molichuxing.services.business.service.VehicleBizService;
import com.molichuxing.services.infrastructure.dto.request.create.InsuranceCommercialCreateDto;
import com.molichuxing.services.infrastructure.dto.request.create.InsuranceCreateDto;
import com.molichuxing.services.infrastructure.dto.request.modify.InsuranceModifyDto;
import com.molichuxing.services.infrastructure.dto.response.*;
import com.molichuxing.services.infrastructure.service.*;
import com.molichuxing.services.property.CarInsuranceNameEnum;
import com.molichuxing.services.property.CarInsuranceTypeEnum;
import com.molichuxing.services.property.CarsOperateStatusEnum;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * 车辆检验bff
 *
 * @author xuelin yi
 */
@Service("insuranceBffService")
public class InsuranceBffServiceImpl implements InsuranceBffService {

    private static final Logger logger = LoggerFactory.getLogger(InsuranceBffServiceImpl.class);

    @Resource
    private CarInsuranceBizService carInsuranceBizService;

    @Resource
    private VehicleBizService vehicleBizService;

    @Resource
    private InsuranceCompanyService insuranceCompanyService;

    @Resource
    private InsuranceService insuranceService;

    @Resource
    private CarService carService;

    @Resource
    private InsuranceCommercialService insuranceCommercialService;

    @Resource
    private FileStorageBffService fileStorageBffService;

    @Resource
    private TokenUtil tokenUtil;

    @Value("${email_account}")
    private String emailAccount;
    @Value("${email_password}")
    private String emailPassword;
    @Value("${import.insurance.pdf.suffix}")
    private String pdfSuffix;

    @Resource
    private VehiclePictureService vehiclePictureService;

    @Override
    public Paged<CarInsuranceVo> getInsurancePage(Map<String, Object> mapParam, Integer pageNum, Integer pageSize) {
        Paged<CarInsuranceDto> result = carInsuranceBizService.getInsurancePage(mapParam, pageNum, pageSize);
        Paged<CarInsuranceVo> resultVo = Convert.toCarInsuranceVoPage(result);
        if ((null != resultVo) && (null != resultVo.getList())) {
            List<CarInsuranceVo> resultList = resultVo.getList();
            for (CarInsuranceVo carInsuranceVo : resultList) {
                carInsuranceVo.setVehicleName(vehicleBizService.getVehicleName(carInsuranceVo.getVehicleId()));
                InsuranceCompanyDto insuranceCompanyDto = insuranceCompanyService.getInsuranceCompanyName(carInsuranceVo.getInsuranceCompanyId());
                if (null != insuranceCompanyDto) {
                    carInsuranceVo.setInsuranceCompanyName(insuranceCompanyDto.getName());
                }
                List<VehiclePictureDto> pictures = vehiclePictureService.getVehiclePictureByVehicleId(carInsuranceVo.getVehicleId());
                if (pictures != null && !pictures.isEmpty()) {
                    for (VehiclePictureDto picture : pictures) {
                        if (picture.getIsWhiteBg() && 1 == picture.getPicDetailType()) {
                            carInsuranceVo.setVehicleImg(picture.getServerUrl());
                            break;
                        }
                    }
                }
            }
        }
        return resultVo;
    }

    @Override
    public List<CarInsuranceVo> getInsuranceList(Integer carId, Integer type) {
        List<CarInsuranceDto> list = carInsuranceBizService.getInsuranceList(carId, type == null?null : CarInsuranceTypeEnum.getEnum(type));
        List<CarInsuranceVo> result = Convert.toCarInsuranceVoPage(list);
        if ((null != result) && (result.size() > 0)) {
            for (CarInsuranceVo carInsuranceVo : result) {
                InsuranceCompanyDto insureCompany = insuranceCompanyService.getInsuranceCompanyName(carInsuranceVo.getInsuranceCompanyId());
                if (null != insureCompany) {
                    carInsuranceVo.setInsuranceCompanyName(insureCompany.getName());
                }
            }
        }
        return result;
    }

    @Override
    public CarInsuranceDetailVo getInsuranceById(int insuranceId) {
        CarInsuranceDetailDto carInsuranceDetailDto = carInsuranceBizService.getInsuranceById(insuranceId);
        if (null != carInsuranceDetailDto) {
            String vehicleName = vehicleBizService.getVehicleName(carInsuranceDetailDto.getVehicleId());
            InsuranceCompanyDto insureCompany = insuranceCompanyService.getInsuranceCompanyName(carInsuranceDetailDto.getInsuranceCompanyId());
            return Convert.toCarInsuranceDetailVo(carInsuranceDetailDto, vehicleName, insureCompany);
        }
        return null;
    }

    @Override
    public Boolean modifyCompulsoryInsurance(CarCompulsoryInsuranceModifyVo modifyDto) throws Exception {
        return carInsuranceBizService.modifyCompulsoryInsurance(Convert.toCarCompulsoryInsuranceModifyDto(modifyDto));
    }

    @Override
    public Boolean modifyCommercialInsurance(CarCommercialInsuranceModifyVo modifyDto) throws Exception {
        return carInsuranceBizService.modifyCommercialInsurance(Convert.toCarCommercialInsuranceModifyDto(modifyDto));
    }

    @Override
    public Boolean uploadInsuranceExcelFile(MultipartFile excelFile, UserEntity currentLoginUser) throws Exception {

        List<String> titleArray = Arrays.asList("*车架号", "*保单号", "*保险公司", "*保费", "*投保日期", "*起保日期", "*终保日期", "*投保人");
        Sheet sheet = getSheet(excelFile, titleArray.size());

        int successNumber = 0; //成功数量
        int failNumber = 0; //失败数量

        //创建两个List集合，一个保存属性文件中的字段值，一个保存Excel文件中每一行的单元格的值
        List<List<String>> errorList = new ArrayList<>();

        //遍历Excel文件
        //获取行数，第一行是标题
        int totalRows = sheet.getPhysicalNumberOfRows();
        if (totalRows >= 2) {
            try {
                for (int i = 0; i < totalRows; i++) {
                    List<String> valuesList = new ArrayList<>();

                    Row row = sheet.getRow(i); //第i行数据

                    int totalCells = row.getPhysicalNumberOfCells(); //第i行的全部列数

                    //获取每个单元格的数据，保存到集合中
                    StringBuffer errorReason = null;

                    try {
                        errorReason = validateImportExcel(sheet, row, titleArray, valuesList);

                        //excel 列名不能操作
                        if (i == 0) {
                            if ((null == valuesList) || (valuesList.size() != 8)) {
                                EmailUtil.sendMail(emailAccount, emailPassword, "交强险信息导入反馈", "模板有误，请重新选择导入文件", tokenUtil.getUser().getAccount());
                                return null;
                            }
                            continue;
                        }

                        //时间初始化，起始时间、投保时间如果是yyyy-MM-dd的格式，在后补0变为yyyy-MM-dd HH:mm:ss格式
                        LocalDateTime insureTime = validateDate(valuesList.get(4), "投保日期", errorReason, "00:00:00"); //投保日期
                        LocalDateTime beginTime = validateDate(valuesList.get(5), "起保日期", errorReason, "00:00:00"); //起保日期
                        LocalDateTime endTime = validateDate(valuesList.get(6), "终保日期", errorReason, "23:59:00"); //终保日期

                        if (!StringUtil.isEmpty(valuesList.get(1)) && valuesList.get(1).length() > 32) {
                            errorReason.append("保单号超出长度，");
                        }

                        CarDto carDto = null;
                        if (!StringUtil.isEmpty(valuesList.get(0))) {
                            carDto = carService.getCarByVin(valuesList.get(0));
                            if (carDto == null) { //校验车辆
                                errorReason.append("车架号不存在，");
                            }
                        }

                        if (!StringUtil.isEmpty(valuesList.get(1))) {
                            InsuranceDto insurance = insuranceService.getInsuranceByCode(valuesList.get(1));
                            if (insurance != null) { //校验保单号是否存在
                                errorReason.append("保单号已经存在，");
                            }
                        }

                        InsuranceCompanyDto insuranceCompany = null;
                        if (!StringUtil.isEmpty(valuesList.get(2))) {
                            insuranceCompany = insuranceCompanyService.getInsuranceByName(valuesList.get(2));
                            if (insuranceCompany == null) { //校验保险公司是否存在
                                errorReason.append("保险公司不存在，");
                            }
                        }


                        if (carDto != null) {
                            InsuranceDto lastInsurance = insuranceService.getLastInsuranceByCarIdAndType
                                    (carDto.getId(), CarInsuranceTypeEnum.COMPULSORY_INSURANCE.getValue());
                            if (lastInsurance != null) {
                                if (beginTime.toEpochSecond(ZoneOffset.of("+8"))
                                        < lastInsurance.getEndTime().toEpochSecond(ZoneOffset.of("+8"))) { //最新投保日期小于上次终保日期
                                    errorReason.append("当前车辆还在保期内，");
                                }
                            }
                        }

                        if (beginTime != null && endTime != null) {
                            if (beginTime.isAfter(endTime)) { //起保日期大于终保日期
                                errorReason.append("起保时间不能大于终保时间，");
                            }
                        }

                        compareToZero(valuesList.get(3), "保费", errorReason);

                        if (StringUtils.isEmpty(errorReason)) {
                            try {
                                InsuranceCreateDto newInsurance = new InsuranceCreateDto();
                                newInsurance.setCarId(carDto.getId()); //车辆id
                                newInsurance.setCode(valuesList.get(1).trim()); //保单号
                                newInsurance.setType(CarInsuranceTypeEnum.COMPULSORY_INSURANCE.getValue()); //交强险
                                newInsurance.setInsuranceCompanyId(insuranceCompany.getId()); //保险公司
                                newInsurance.setCost(new BigDecimal(valuesList.get(3))); //保费
                                newInsurance.setInsureTime(insureTime); //投保时间
                                newInsurance.setBeginTime(beginTime); //起保日期
                                newInsurance.setEndTime(endTime); //终保日期
                                newInsurance.setPolicyHolder(valuesList.get(7)); //投保人
                                Boolean result = insuranceService.createInsurance(newInsurance);
                                if (!result) {
                                    errorReason.append("增加交强险失败，");
                                    failNumber++;
                                } else {
                                    if (!carDto.getIsHaveCompulsory()) {
                                        CarModifyDto dto = new CarModifyDto();
                                        dto.setId(carDto.getId());
                                        dto.setIsHaveCompulsory(true);
                                        carService.modify(dto); //修改车辆状态
                                        CarDto carData = carService.getCarById(carDto.getId());
                                        if (carData.getIsHavePlate() && carData.getIsHaveCompulsory() && carData.getIsHaveCommercial() && carData.getIsInstallTbox()) {
                                            carService.modifyStatusById(carDto.getId(), CarsOperateStatusEnum.FOR_RENT);
                                        }
                                    }
                                }
                            } catch (Exception e) {
                                logger.error("【uploadInsuranceExcelFile】导入交强险excel失败", e);
                                errorReason.append("数据有误，");
                            }
                        }
                    } finally {
                        if (StringUtils.isNotEmpty(errorReason)) {
                            valuesList.add(errorReason.toString());
                            errorList.add(valuesList);
                            failNumber++;
                        } else {
                            successNumber++;
                        }
                    }
                }
            } finally {
                //发送邮件提醒
                if (null == errorList || errorList.size() == 0) {
                    EmailUtil.sendMail(emailAccount, emailPassword, "交强险信息导入反馈", "【交强险信息】" + successNumber + "条导入成功", currentLoginUser.getAccount());
                } else {
                    //生成EXCEL
                    XSSFWorkbook xssBook = PoiExcelUtils.createXSSFWorkbook(titleArray, errorList);
                    ByteArrayOutputStream os = new ByteArrayOutputStream();
                    try {
                        xssBook.write(os);
                    } catch (IOException e) {
                        logger.error(e.getMessage(), e);
                    }
                    EmailUtil.sendMail(emailAccount, emailPassword, "交强险信息导入反馈", "【交强险信息】" + successNumber + "条导入成功，" + failNumber + "条导入失败！可下载详情失败数据", currentLoginUser.getAccount(),
                            "交强险导入.xlsx", new ByteArrayInputStream(os.toByteArray()));
                }
            }
        }
        return true;
    }

    @Override
    public Boolean uploadInsurancePdfFile(MultipartFile pdfFile, UserEntity currentLoginUser, OSSFileFolderEnum folder) throws Exception {
        // 开始解压
        try {
            List<List<String>> errorList = new ArrayList<>();
            Integer successNumber = new Integer(0);

            UploadFileRequestDto uploadFileRequestDto = fileStorageBffService.getFileStorageConfig(folder.getName());
            if (uploadFileRequestDto != null) {
                List<UploadFileResponseDto> filePathList = UploadUtil.uploadZipFileToAll(pdfFile, uploadFileRequestDto);
                successNumber = updatePdfPath(filePathList, 1, errorList, successNumber);
            }

            if (null == errorList || errorList.size() == 0) {
                if (folder.getValue() == OSSFileFolderEnum.INSURANCE.getValue()) { //交强险
                    EmailUtil.sendMail(emailAccount, emailPassword, "交强险PDF保单导入反馈", DateUtil.formatDateNow() + "【交强险PDF保单】" + successNumber + "条导入成功", currentLoginUser.getAccount());
                } else { //商业险
                    EmailUtil.sendMail(emailAccount, emailPassword, "商业险PDF保单导入反馈", DateUtil.formatDateNow() + "【商业险PDF保单】" + successNumber + "条导入成功", currentLoginUser.getAccount());
                }

            } else {
                List<String> titleArray = Arrays.asList("*文件名称", "*失败原因");

                //生成EXCEL
                XSSFWorkbook xssBook = PoiExcelUtils.createXSSFWorkbook(titleArray, errorList);
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                try {
                    xssBook.write(os);
                } catch (IOException e) {
                    e.printStackTrace();
                }

                if (folder.getValue() == OSSFileFolderEnum.INSURANCE.getValue()) { //交强险
                    EmailUtil.sendMail(emailAccount, emailPassword, "交强险PDF保单导入反馈", DateUtil.formatDateNow() + "【交强险PDF保单】" + successNumber + "条导入成功，" + errorList.size() + "条导入失败！可下载详情失败数据", currentLoginUser.getAccount(),
                            "交强险PDF.xlsx", new ByteArrayInputStream(os.toByteArray()));
                } else { //商业险
                    EmailUtil.sendMail(emailAccount, emailPassword, "商业险PDF保单导入反馈", DateUtil.formatDateNow() + "【商业险PDF保单】" + successNumber + "条导入成功，" + errorList.size() + "条导入失败！可下载详情失败数据", currentLoginUser.getAccount(),
                            "商业险PDF.xlsx", new ByteArrayInputStream(os.toByteArray()));
                }

            }
        } catch (Exception e) {
            logger.error("[uploadInsurancePdfFile]导入保单PDF异常", e);
        }

        return true;
    }

    private Integer updatePdfPath(List<UploadFileResponseDto> filePathList, int type, List<List<String>> errorList, Integer successNumber) {
        if (CollectionUtils.isNotEmpty(filePathList)) {
            for (UploadFileResponseDto responseDto : filePathList) {
                List<String> errorStr = new ArrayList<>();

                String fileName = responseDto.getFileName();
                errorStr.add(fileName);
                if (fileName.indexOf(".pdf") == -1) {
                    errorStr.add("文件格式错误,");
                    errorList.add(errorStr);
                } else {
                    if (!StringUtil.isEmpty(fileName)) {
                        fileName = fileName.split("\\.")[0];
                        String code = "";
                        if (fileName.endsWith(pdfSuffix)) {
                            code = fileName.substring(0, fileName.length() - pdfSuffix.length());
                        } else {
                            errorStr.add("文件名称格式不正确，");
                        }

                        InsuranceDto insurance = null;
                        if (!StringUtil.isEmpty(code)) {
                            insurance = insuranceService.getInsuranceByCode(code);
                        }

                        if (insurance != null && StringUtil.isEmpty(insurance.getInsureProve())) {
                            InsuranceModifyDto modifyDto = Convert.toInsuranceModifyDto(insurance);
                            if (StringUtil.isEmpty(insurance.getInsureProve())) {
                                modifyDto.setInsureProve(responseDto.getFileUrl());
                            }

                            if (StringUtil.isEmpty(insurance.getInsureProveImg())) {
                                modifyDto.setInsureProveImg(responseDto.getImgUrl());
                            }
                            Boolean result = insuranceService.modifyCompulsoryInsurance(modifyDto);
                            if (result) {
                                successNumber++;
                            }
                        } else if (insurance != null && !StringUtil.isEmpty(insurance.getInsureProve())) {
                            errorStr.add("保单pdf已经存在，");
                        } else {
                            errorStr.add("保单不存在，");
                        }
                    } else {
                        errorStr.add("文件名称不存在，");
                    }
                }
                if (errorStr.size() > 1) {
                    errorList.add(errorStr);
                }
            }
        }
        return successNumber;
    }

    @Override
    public Boolean uploadInsuranceCommercialExcelFile(MultipartFile excelFile, UserEntity currentLoginUser) throws Exception {
        List<String> titleArray = Arrays.asList("*车架号", "*保单号", "*保险公司", "*车辆损失险保额", "车辆损失险保费", "*第三者责任险保额", "第三者责任险保费", "*基本不计免赔保额", "基本不计免赔保费", "*保费合计", "*投保日期", "*起保日期", "*终保日期", "*投保人");
        Sheet sheet = getSheet(excelFile, titleArray.size());

        int successNumber = 0; //成功数量
        int failNumber = 0; //失败数量

        //创建两个List集合，一个保存属性文件中的字段值，一个保存Excel文件中每一行的单元格的值
        List<List<String>> errorList = new ArrayList<>();

        //遍历Excel文件
        //获取行数，第一行是标题
        int totalRows = sheet.getPhysicalNumberOfRows();

        if (totalRows >= 2) {
            for (int i = 0; i < totalRows; i++) {
                List<String> valuesList = new ArrayList<>();

                Row row = sheet.getRow(i); //第i行数据

                int totalCells = row.getPhysicalNumberOfCells(); //第i行的全部列数

                //获取每个单元格的数据，保存到集合中
                StringBuffer errorReason = validateImportExcel(sheet, row, titleArray, valuesList);

                //excel 列名不能操作
                if (i == 0) {
                    if ((null == valuesList) || (valuesList.size() != titleArray.size())) {
                        EmailUtil.sendMail(emailAccount, emailPassword, "商业险信息导入反馈", "模板有误，请重新选择导入文件", tokenUtil.getUser().getAccount());
                        return null;
                    }
                    continue;
                }

                //时间初始化，起始时间、投保时间如果是yyyy-MM-dd的格式，在后补0变为yyyy-MM-dd HH:mm:ss格式
                LocalDateTime insureTime = validateDate(valuesList.get(10), "投保日期", errorReason, "00:00:00"); //投保日期
                LocalDateTime beginTime = validateDate(valuesList.get(11), "起保日期", errorReason, "00:00:00"); //起保日期
                LocalDateTime endTime = validateDate(valuesList.get(12), "终保日期", errorReason, "23:59:00"); //终保日期

                CarDto carDto = null;
                if (!StringUtil.isEmpty(valuesList.get(0))) {
                    carDto = carService.getCarByVin(valuesList.get(0));
                    if (carDto == null) { //校验车辆
                        errorReason.append("车架号不存在，");
                    }
                }

                if (!StringUtil.isEmpty(valuesList.get(1))) {
                    InsuranceDto insurance = insuranceService.getInsuranceByCode(valuesList.get(1));
                    if (insurance != null) { //校验保单号是否存在
                        errorReason.append("保单号已经存在，");
                    }
                }

                InsuranceCompanyDto insuranceCompany = null;
                if (!StringUtil.isEmpty(valuesList.get(2))) {
                    insuranceCompany = insuranceCompanyService.getInsuranceByName(valuesList.get(2));
                    if (insuranceCompany == null) { //校验保险公司是否存在
                        errorReason.append("保险公司不存在，");
                    }
                }

                if (beginTime != null && endTime != null) {
                    if (beginTime.isAfter(endTime)) { //起保日期大于终保日期
                        errorReason.append("起保时间不能大于终保时间，");
                    }
                }

                compareToZero(valuesList.get(9), "保费合计", errorReason); //保费验证
                compareToZero(valuesList.get(3), "车辆损失险保额", errorReason); //车辆损失险-保额验证
                compareToZero(valuesList.get(4), "车辆损失险保费", errorReason); //车辆损失险-保费验证
                compareToZero(valuesList.get(5), "第三者责任险保额", errorReason); //第三者责任险-保额验证
                compareToZero(valuesList.get(6), "第三者责任险保费", errorReason); //第三者责任险-保费验证
                compareToZero(valuesList.get(7), "基本不计免赔保额", errorReason); //基本不计免赔-保额验证
                compareToZero(valuesList.get(8), "基本不计免赔保费", errorReason); //基本不计免赔-保费验证

                if (StringUtils.isEmpty(errorReason)) {
                    try {
                        InsuranceCreateDto newInsurance = new InsuranceCreateDto();
                        newInsurance.setCarId(carDto.getId()); //车辆id
                        newInsurance.setCode(valuesList.get(1)); //保单号
                        newInsurance.setType(CarInsuranceTypeEnum.COMMERCIAL_INSURANCE.getValue()); //交强险
                        newInsurance.setInsuranceCompanyId(insuranceCompany.getId()); //保险公司
                        newInsurance.setCost(new BigDecimal(valuesList.get(9))); //保费
                        newInsurance.setInsureTime(insureTime); //投保时间
                        newInsurance.setBeginTime(beginTime); //起保日期
                        newInsurance.setEndTime(endTime); //终保日期
                        newInsurance.setPolicyHolder(valuesList.get(13)); //投保人
                        Integer insuranceId = insuranceService.createImportInsurance(newInsurance);
                        if (insuranceId > 0) {
                            List<InsuranceCommercialCreateDto> newDataList = new ArrayList<>();
                            //车辆损失险
                            InsuranceCommercialCreateDto lossInsurance = new InsuranceCommercialCreateDto();
                            lossInsurance.setInsureId(insuranceId);
                            lossInsurance.setName(CarInsuranceNameEnum.VEHICLE_LOSS_LNSURANCE.getValue());
                            lossInsurance.setAmount(new BigDecimal(valuesList.get(3))); //保额
                            if (!StringUtil.isEmpty(valuesList.get(4))) {
                                lossInsurance.setCost(new BigDecimal(valuesList.get(4))); //保费
                            }
                            newDataList.add(lossInsurance);

                            //第三者责任险
                            InsuranceCommercialCreateDto thirdInsurance = new InsuranceCommercialCreateDto();
                            thirdInsurance.setInsureId(insuranceId);
                            thirdInsurance.setName(CarInsuranceNameEnum.THIRD_PARTY_LIABILITY_INSURANCE.getValue());
                            thirdInsurance.setAmount(new BigDecimal(valuesList.get(5))); //保额
                            if (!StringUtil.isEmpty(valuesList.get(6))) {
                                thirdInsurance.setCost(new BigDecimal(valuesList.get(6))); //保费
                            }
                            newDataList.add(thirdInsurance);

                            //基本不计免赔
                            InsuranceCommercialCreateDto noDeductible = new InsuranceCommercialCreateDto();
                            noDeductible.setInsureId(insuranceId);
                            noDeductible.setName(CarInsuranceNameEnum.BASICALLY_NO_DEDUCTIBLE.getValue());
                            noDeductible.setAmount(new BigDecimal(valuesList.get(7))); //保额
                            if (!StringUtil.isEmpty(valuesList.get(8))) {
                                noDeductible.setCost(new BigDecimal(valuesList.get(8))); //保费
                            }
                            newDataList.add(noDeductible);

                            int addCount = insuranceCommercialService.createBatchCommercial(newDataList);
                            if (addCount <= 0) {
                                errorReason.append("增加商业险失败，");
                            }

                            if (!carDto.getIsHaveCommercial()) {
                                CarModifyDto dto = new CarModifyDto();
                                dto.setId(carDto.getId());
                                dto.setIsHaveCommercial(true);
                                carService.modify(dto); //修改车辆状态
                                CarDto carData = carService.getCarById(carDto.getId());
                                if(carData.getIsHavePlate()&&carData.getIsHaveCompulsory()&&carData.getIsHaveCommercial()&&carData.getIsInstallTbox()) {
                                    carService.modifyStatusById(carDto.getId(), CarsOperateStatusEnum.FOR_RENT);
                                }
                            }
                        } else {
                            errorReason.append("增加商业险失败，");
                        }
                    } catch (Exception e) {
                        logger.error("【uploadInsuranceCommercialExcelFile】导入交强险excel失败", e);
                        errorReason.append("数据有误，");
                    }

                }

                if (StringUtils.isNotEmpty(errorReason)) {
                    valuesList.add(errorReason.toString());
                    errorList.add(valuesList);
                    failNumber++;
                } else {
                    successNumber++;
                }
            }

            //发送邮件提醒
            if (null == errorList || errorList.size() == 0) {
                EmailUtil.sendMail(emailAccount, emailPassword, "商业险信息导入反馈", "【商业险信息】" + successNumber + "条导入成功", currentLoginUser.getAccount());
            } else {
                //生成EXCEL
                XSSFWorkbook xssBook = PoiExcelUtils.createXSSFWorkbook(titleArray, errorList);
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                try {
                    xssBook.write(os);
                } catch (IOException e) {
                    logger.error(e.getMessage(), e);
                }
                EmailUtil.sendMail(emailAccount, emailPassword, "商业险信息导入反馈", "【商业险信息】" + successNumber + "条导入成功，" + failNumber + "条导入失败！可下载详情失败数据", currentLoginUser.getAccount(),
                        "商业险导入.xlsx", new ByteArrayInputStream(os.toByteArray()));
            }
        }
        return true;
    }

    private void compareToZero(String value, String name, StringBuffer errorReason) {
        if (!StringUtil.isEmpty(value)) {
            try {
                if (new BigDecimal(value).compareTo(BigDecimal.ZERO) < 0) {
                    logger.error("[compareToZero](" + name + ")(" + value + ")小于0");
                    errorReason.append(name + "不能小于0，");
                }
            } catch (Exception e) {
                logger.error("[compareToZero](" + name + ")(" + value + ")转换BigDecimal错误", e);
                errorReason.append(name + "格式错误，");
            }
        }
    }

    private LocalDateTime validateDate(String value, String name, StringBuffer errorReason, String suffix) {
        LocalDateTime time = null; //投保日期
        if (!StringUtil.isEmpty(value) && value.length() == DatePattern.NORM_DATE_PATTERN.length()) {
            try {
                time = DateUtil.parseDateTime(value + " "+ suffix, DatePattern.NORM_DATETIME_PATTERN); //起保日期
            } catch (Exception e) {
                logger.error("[validateDate]({})({})格式错误", name, value);
                errorReason.append(name + "格式错误，");
            }
        } else {
            logger.error("[validateDate]({})({})格式错误", name, value);
            errorReason.append(name + "格式错误，");
        }
        return time;
    }

    private StringBuffer validateImportExcel(Sheet sheet, Row row, List<String> titleArray, List<String> valuesList) {
        StringBuffer errorReason = new StringBuffer();
        int totalCells = row.getPhysicalNumberOfCells(); //第i行的全部列数
        for (int t = 0; t < titleArray.size(); t++) {

            String cellInfo = null;

            Cell cell = row.getCell(t);
            if (cell != null) {
                CellType cellTypeEnum = cell.getCellTypeEnum();
                if (cellTypeEnum.getCode() == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        if (cell.getDateCellValue() != null) {
                            SimpleDateFormat dateFormat = new SimpleDateFormat(DatePattern.NORM_DATE_PATTERN);
                            cellInfo = dateFormat.format(cell.getDateCellValue());
                        }
                    } else {
                        cell.setCellType(CellType.STRING);
                        cellInfo = cell.getStringCellValue();
                    }
                } else {
                    cell.setCellType(CellType.STRING);
                    cellInfo = cell.getStringCellValue();
                }
                if (StringUtils.isEmpty(cellInfo) && titleArray.get(t).startsWith("*")) {
                    errorReason.append(titleArray.get(t) + "不能为空，");
                }
            } else {
                if (StringUtils.isEmpty(cellInfo)) {
                    cellInfo = "";
                    if (titleArray.get(t).startsWith("*")) {
                        errorReason.append(titleArray.get(t) + "不能为空，");
                    }
                }
            }
            valuesList.add(cellInfo);


        }
        return errorReason;
    }

    private Sheet getSheet(MultipartFile excelFile, int lineCount) throws IOException {
        //判断Excel文件的版本
        boolean isExcel2003 = true;
        if (excelFile.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = excelFile.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        //获取Excel文件的第一页sheet，判断是否有信息
        Sheet sheet = wb.getSheetAt(0);
        Row row = sheet.getRow(0);
        if (row.getPhysicalNumberOfCells() != lineCount) {
            throw new BizException(ResponseCodeEnum.UNKNOWN_ERROR.getCode(), "上传文件格式不正确");
        }

        return sheet;
    }
}
